using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;

namespace TIS.DAO
{
	public class Category : DBInteractionBase
	{
		#region Class Member Declarations
			private SqlDateTime		_dateInsert, _dateUpdate;
			private SqlInt32		_categoryStatus, _languageId, _categoryPosition, _categoryId, _usertUpdate, _userInsert, _categoryParent, _categoryDisplayCenter;
			private SqlString		_categoryTitle, _categoryName, _categorySEOUrl, _categoryKeyword, _categoryDescription;
		#endregion


		public Category()
		{
			// Nothing for now.
		}


		public override bool Insert()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_Category_Insert]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategoryName", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categoryName));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryParent", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _categoryParent));
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategoryTitle", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categoryTitle));
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategoryDescription", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categoryDescription));
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategoryKeyword", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categoryKeyword));
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategorySEOUrl", SqlDbType.NChar, 10, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categorySEOUrl));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryDisplayCenter", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _categoryDisplayCenter));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryPosition", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _categoryPosition));
				cmdToExecute.Parameters.Add(new SqlParameter("@iLanguageId", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _languageId));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryStatus", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _categoryStatus));
				cmdToExecute.Parameters.Add(new SqlParameter("@iUserInsert", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _userInsert));
				cmdToExecute.Parameters.Add(new SqlParameter("@iUsertUpdate", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _usertUpdate));
				cmdToExecute.Parameters.Add(new SqlParameter("@daDateInsert", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _dateInsert));
				cmdToExecute.Parameters.Add(new SqlParameter("@daDateUpdate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _dateUpdate));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryId", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, _categoryId));

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				cmdToExecute.ExecuteNonQuery();
				_categoryId = (Int32)cmdToExecute.Parameters["@iCategoryId"].Value;
				return true;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("Category::Insert::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
			}
		}


		public override bool Update()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_Category_Update]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryId", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _categoryId));
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategoryName", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categoryName));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryParent", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _categoryParent));
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategoryTitle", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categoryTitle));
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategoryDescription", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categoryDescription));
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategoryKeyword", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categoryKeyword));
				cmdToExecute.Parameters.Add(new SqlParameter("@sCategorySEOUrl", SqlDbType.NChar, 10, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _categorySEOUrl));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryDisplayCenter", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _categoryDisplayCenter));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryPosition", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _categoryPosition));
				cmdToExecute.Parameters.Add(new SqlParameter("@iLanguageId", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _languageId));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryStatus", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _categoryStatus));
				cmdToExecute.Parameters.Add(new SqlParameter("@iUserInsert", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _userInsert));
				cmdToExecute.Parameters.Add(new SqlParameter("@iUsertUpdate", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _usertUpdate));
				cmdToExecute.Parameters.Add(new SqlParameter("@daDateInsert", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _dateInsert));
				cmdToExecute.Parameters.Add(new SqlParameter("@daDateUpdate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _dateUpdate));

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				cmdToExecute.ExecuteNonQuery();
				return true;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("Category::Update::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
			}
		}


		public override bool Delete()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_Category_Delete]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryId", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _categoryId));

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				cmdToExecute.ExecuteNonQuery();
				return true;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("Category::Delete::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
			}
		}


		public override DataTable SelectOne()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_Category_SelectOne]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;
			DataTable toReturn = new DataTable("Category");
			SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryId", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _categoryId));

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				adapter.Fill(toReturn);
				if(toReturn.Rows.Count > 0)
				{
					_categoryId = (Int32)toReturn.Rows[0]["CategoryId"];
					_categoryName = toReturn.Rows[0]["CategoryName"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["CategoryName"];
					_categoryParent = toReturn.Rows[0]["CategoryParent"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["CategoryParent"];
					_categoryTitle = toReturn.Rows[0]["CategoryTitle"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["CategoryTitle"];
					_categoryDescription = toReturn.Rows[0]["CategoryDescription"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["CategoryDescription"];
					_categoryKeyword = toReturn.Rows[0]["CategoryKeyword"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["CategoryKeyword"];
					_categorySEOUrl = toReturn.Rows[0]["CategorySEOUrl"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["CategorySEOUrl"];
					_categoryDisplayCenter = toReturn.Rows[0]["CategoryDisplayCenter"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["CategoryDisplayCenter"];
					_categoryPosition = toReturn.Rows[0]["CategoryPosition"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["CategoryPosition"];
					_languageId = toReturn.Rows[0]["LanguageId"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["LanguageId"];
					_categoryStatus = (Int32)toReturn.Rows[0]["CategoryStatus"];
					_userInsert = toReturn.Rows[0]["UserInsert"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["UserInsert"];
					_usertUpdate = toReturn.Rows[0]["UsertUpdate"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["UsertUpdate"];
					_dateInsert = toReturn.Rows[0]["DateInsert"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["DateInsert"];
					_dateUpdate = toReturn.Rows[0]["DateUpdate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["DateUpdate"];
				}
				return toReturn;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("Category::SelectOne::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
				adapter.Dispose();
			}
		}


		public override DataTable SelectAll()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_Category_SelectAll]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;
			DataTable toReturn = new DataTable("Category");
			SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				adapter.Fill(toReturn);
				return toReturn;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("Category::SelectAll::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
				adapter.Dispose();
			}
		}


		#region Class Property Declarations
		public SqlInt32 CategoryId
		{
			get
			{
				return _categoryId;
			}
			set
			{
				SqlInt32 categoryIdTmp = (SqlInt32)value;
				if(categoryIdTmp.IsNull)
				{
					throw new ArgumentOutOfRangeException("CategoryId", "CategoryId can't be NULL");
				}
				_categoryId = value;
			}
		}


		public SqlString CategoryName
		{
			get
			{
				return _categoryName;
			}
			set
			{
				_categoryName = value;
			}
		}


		public SqlInt32 CategoryParent
		{
			get
			{
				return _categoryParent;
			}
			set
			{
				_categoryParent = value;
			}
		}


		public SqlString CategoryTitle
		{
			get
			{
				return _categoryTitle;
			}
			set
			{
				_categoryTitle = value;
			}
		}


		public SqlString CategoryDescription
		{
			get
			{
				return _categoryDescription;
			}
			set
			{
				_categoryDescription = value;
			}
		}


		public SqlString CategoryKeyword
		{
			get
			{
				return _categoryKeyword;
			}
			set
			{
				_categoryKeyword = value;
			}
		}


		public SqlString CategorySEOUrl
		{
			get
			{
				return _categorySEOUrl;
			}
			set
			{
				_categorySEOUrl = value;
			}
		}


		public SqlInt32 CategoryDisplayCenter
		{
			get
			{
				return _categoryDisplayCenter;
			}
			set
			{
				_categoryDisplayCenter = value;
			}
		}


		public SqlInt32 CategoryPosition
		{
			get
			{
				return _categoryPosition;
			}
			set
			{
				_categoryPosition = value;
			}
		}


		public SqlInt32 LanguageId
		{
			get
			{
				return _languageId;
			}
			set
			{
				_languageId = value;
			}
		}


		public SqlInt32 CategoryStatus
		{
			get
			{
				return _categoryStatus;
			}
			set
			{
				SqlInt32 categoryStatusTmp = (SqlInt32)value;
				if(categoryStatusTmp.IsNull)
				{
					throw new ArgumentOutOfRangeException("CategoryStatus", "CategoryStatus can't be NULL");
				}
				_categoryStatus = value;
			}
		}


		public SqlInt32 UserInsert
		{
			get
			{
				return _userInsert;
			}
			set
			{
				_userInsert = value;
			}
		}


		public SqlInt32 UsertUpdate
		{
			get
			{
				return _usertUpdate;
			}
			set
			{
				_usertUpdate = value;
			}
		}


		public SqlDateTime DateInsert
		{
			get
			{
				return _dateInsert;
			}
			set
			{
				_dateInsert = value;
			}
		}


		public SqlDateTime DateUpdate
		{
			get
			{
				return _dateUpdate;
			}
			set
			{
				_dateUpdate = value;
			}
		}
		#endregion
	}
}
